# -*- coding: utf-8 -*-

# 文件中封装了一下常规的统计数据的获取方式

from system.DBManage import MysqlDB

from system.Config import *


def seven_day_count():
    """
    统计近七天的测试数据,这样的统计数据一般构建好之后就不会经常去变更统计方式的；
    统计维度为：
        1、测试的用例执行数量
        2、测试用例的异常情况

    数据库设计要求：
        1、数据库中必须包含用例时间维度，否则无从统计近七天的数据
        2、数据中必须包含每条用例的执行结果，否则无从统计异常情况
    :return:
    """
    results = dict()
    dates = list()
    tests = list()
    errs = list()

    """
    学习的时候，使用sqlite3 数据库进行调试：
    # 获取近七天的数据sql语句
    select * from test_cases where createTime>=datetime('now','start of day','-7 day') and createTime<=datetime('now','start of day','+1 day') ORDER BY createTime;
    # 统计某天的测试用例数据sql语句
    select count(*) as count from test_cases where createTime like '2020-02-01%';
    # 通过某天的异常数据sql语句
    select count(*) from test_cases where createTime like '2020-02-01%' and testResult !='passed';

    """

    """
    正式生产尽量使用mysql 数据库进行调试：
    # 获取近七天的数据sql语句
    SELECT * FROM test_cases WHERE date_sub(curdate(), interval 7 day) <= datetime;
    # 统计某天的测试用例数据sql语句
    select count(*) as count from test_cases where datetime like '2020-02-01%';
    # 统计某天的异常数据sql语句
    select  count(*) as count from test_cases where datetime like '2020-02-01%' and result != 'passed';
    """
    # 连接数据库
    mysql = MysqlDB(host=host, user=user, password=password, database=database, port=port)
    # 查询数据库中近七天的情况
    dql_result = mysql.query('''SELECT * FROM test_cases_1 WHERE date_sub(curdate(), interval 7 day) <= datetime;''')
    for i in dql_result:
        date = str(i["datetime"]).split(" ")[0]  # 处理日期格式
        if date not in dates:  # 过滤重复的日期数据
            dates.append(date)  # 将处理好的日期存储到列表中去

    for i in dates:  # 通过遍历日期获取每天的数据情况
        dql_result = mysql.query(f"""select count(*) as count from test_cases_1 where datetime like '{i}%';""")
        tests.append(dql_result[0]["count"])

        dql_result = mysql.query(
            f"""    select  count(*) as count from test_cases_1 where datetime like '{i}%' and result != 'passed';""")
        errs.append(dql_result[0]["count"])

    # 组装数据，并生成一个字典
    results.update({
        "dates": dates,
        "tests": tests,
        "errs": errs,
    })

    return results


def team_count():
    """
    以项目团队为维度去统计各项目的质量情况
    :return:
    """
    results = dict()

    teams = list()
    tests = list()
    errs = list()

    mysql = MysqlDB(host=host, user=user, password=password, database=database, port=port)

    # 获取所有的团队名称
    dql_result = mysql.query("select team from test_cases_1 group by team;")
    for i in dql_result:
        teams.append(i["team"])

    # 通过团队名称获取每个团队的用例情况；
    for i in teams:
        dql_result = mysql.query(f"""select count(*) as count from test_cases_1 where team='{i}';""")
        tests.append(dql_result[0]["count"])

    # 通过团队名称获取每个团队的异常情况；
    for i in teams:
        dql_result = mysql.query(f"""select count(*) as count from test_cases_1 where team='{i}' and result!='passed';""")
        errs.append(dql_result[0]["count"])

    results.update({
        "teams": teams,
        "tests": tests,
        "errs": errs,
    })

    return results


if __name__ == '__main__':
    # seven_day_count()
    print(team_count())
